from ftplib import FTP
from tqdm import tqdm
from threading import Thread
from queue import Queue
from sql import sqlseverDB
from mini_tools.ftpLoad import downloadImg
import os,re,requests,time

ms = sqlseverDB('{SQL Server}', '199.33.126.84,5810', 'shopeecoid', 'sa', 'Y3GwLHaP8zQ0seZG')
session=requests.session()
load_task=Queue(-1)
req_task=Queue(-1)
def get_pid_byurl(url):

    pat_pid = re.compile(r'-i[.](\d+?)[.](\d+)[?]?')
    pat_pid2 = re.compile(r'product/(\d+?)/(\d+?)/')
    mat = pat_pid.search(url)
    if not mat:
        mat = pat_pid2.search(url)
    if mat:
        return (mat.group(1), mat.group(2))
    else:
        return ''
    

def reqImg(dirpath,imgname):
    
    try:
        if os.path.exists(f'{dirpath}/{imgname}.jpg'):
            return
        cot=session.get(f'https://down-ph.img.susercontent.com/file/{imgname}').content
        with open(f'{dirpath}/{imgname}.jpg','wb') as f:
            f.write(cot)
        #print(f'成功下载图片path/{img}.jpg')
    except Exception as e:

        print(e)

def get_data():


    while True:
        rs=ms.ExecQuery('''select [id],[TaRenChanPinLianJie],[PingLunID],[MaiJiaXiuTuPianWangZhi1],[MaiJiaXiuTuPianWangZhi2],[MaiJiaXiuTuPianWangZhi3],
                        [MaiJiaXiuTuPianWangZhi4],[MaiJiaXiuTuPianWangZhi5] from [comment] where [ShangJiaBianMa]='LQS888' and [isDwonloadImg]=0''')
        for r in rs:
            load_task.put([r[0],r[1],r[2],list(r)[3:]])
        
        time.sleep(20)
        
def load_img():

    while True:
        if not load_task.empty():
            try:
                zzid,talj,cmtid,imgpaths=load_task.get()
                if not get_pid_byurl(talj):
                    continue
                shopid,itemid=get_pid_byurl(talj)
                dirpath=f'D:/ShopeeImg/{shopid}-{itemid}-{cmtid}'
                if not os.path.exists(dirpath):
                    os.mkdir(dirpath)

                for img in imgpaths:
                    if img:
                        mat=re.search(r'.*/(.*?).jpg',img)
                        if mat:
                            imgname=mat.group(1)
                        reqImg(dirpath,imgname)
                
                if len(imgpaths)==0:
                    #没图片将该条评论状态标注为2
                    aff=ms.ExecNoQuery('update [comment] set [isDwonloadImg]=?,[picdir]=? where [id]=?',(2,f'{shopid}-{itemid}-{cmtid}',zzid))
                    print(f"【数据库日志】更新 {shopid}-{itemid}-{cmtid} 无图片,状态值为2 =>{'成功' if aff else '失败'}")
                
                else:
                    #下载完图片将该条评论状态标注为1
                    aff=ms.ExecNoQuery('update [comment] set [isDwonloadImg]=?,[picdir]=? where [id]=?',(1,f'{shopid}-{itemid}-{cmtid}',zzid))
                    print(f"【数据库日志】更新 {shopid}-{itemid}-{cmtid} 图片下载{len(imgpaths)}张,状态更改为1 =>{'成功' if aff else '失败'}")
            except Exception as e:

                print(e)

               
def main():

    Thread(target=get_data).start()

    #开20个线程下载图片
    xc_load_imgs=[]
    for j in range(10):
        t0=Thread(target=load_img)
        xc_load_imgs.append(t0)
        t0.start()

    while True:
        try:
            rs=ms.ExecQuery("select count(id) from comment where ShangJiaBianMa='LQS888' and isDwonloadImg=0")
            print(f'当前未下载图片的评论 {rs[0][0]} 个')
            time.sleep(10)
        except Exception as e:
            print(e)

    # 3. 关闭FTP连接
    # ftp.quit()

if __name__=='__main__':

    main()